CREATE EXTERNAL TABLE `jms_tmp`.`tmp_dm_oper_center_ship_port_detail_dt`
(
    send_waybill_no               string COMMENT '发件运单号'
   ,send_shipment_no              string COMMENT '发件任务号'
   ,send_scan_site_code           string COMMENT '发件当前站'
   ,send_scan_site_name           string COMMENT '发件当前站'
   ,send_next_site_code           string COMMENT '发件下一站'
   ,send_package_code             string COMMENT '发件包号'
   ,send_scan_time                string COMMENT '发件扫描时间'
   ,ship_shipment_no              string COMMENT '任务号'
   ,ship_shipment_state           string COMMENT '任务状态'
   ,ship_sort_num                 string COMMENT '任务站点顺序'
   ,ship_center_code              string COMMENT '任务号中心code'
   ,ship_center_name              string COMMENT '任务号中心name'
   ,arrival_waybill_no            string COMMENT '到件运单号'
   ,arrival_shipment_no           string COMMENT '到件任务号'
   ,arrival_package_code          string COMMENT '到件包号'
   ,arrival_scan_time             string COMMENT '到件扫描时间'
   ,arrival_scan_site_code        string COMMENT '到件扫描站点code'
   ,arrival_scan_site_name        string COMMENT '到件扫描站点'
   ,send_next_waybill_no          string COMMENT '发件下一站运单号'
   ,send_next_shipment_no         string COMMENT '发件下一站任务号'
   ,send_next_scan_site_code      string COMMENT '发件下一站扫描站点code'
   ,send_next_scan_site_name      string COMMENT '发件下一站扫描站点'
   ,send_next_next_site_code      string COMMENT '发件下一站下一站点code'
   ,send_next_package_code        string COMMENT '发件下一站包号'
   ,send_next_scan_time           string COMMENT '发件下一站扫描时间'
   ,planned_arrival_time          string COMMENT '计划到达日期+时间'
   ,planned_departure_time        string COMMENT '计划离开日期+时间'
   ,actual_arrival_time           string COMMENT '实际到达时间'
   ,actual_departure_time         string COMMENT '实际离开时间'
   ,unlock_time                   string COMMENT '解锁时间'
   ,zt_car                        string COMMENT '在途车辆(任务号)'
   ,zt_piece                      string COMMENT '在途件量'
   ,zt_part_piece                 string COMMENT '在途件量剔除包内件(运单号)'
   ,zt_scan_piece                 string COMMENT '在途件量剔除包号(运单号)'
   ,zt_bag_piece                  string COMMENT '在途件量剔除散单和包内件(运单号)'
   ,yx_piece                      string COMMENT '已卸件量(运单号)'
   ,yx_part_piece                 string COMMENT '已卸件量剔除包内件(运单号)'
   ,yx_scan_piece                 string COMMENT '已卸件量剔除包号(运单号)'
   ,yx_bag_piece                  string COMMENT '已卸件量剔除散单和包内件(运单号)'
   ,yxwf_piece                    string COMMENT '已卸未发件量(运单号)'
   ,yxwf_part_piece               string COMMENT '已卸未发剔除包内件件量(运单号)'
   ,yxwf_scan_piece               string COMMENT '已卸未发剔除包号件量(运单号)'
   ,yxwf_bag_piece                string COMMENT '已卸未发剔除散单和包内件件量(运单号)'
   ,all_piece                     string COMMENT '全部件量(运单号)'
   ,all_part_piece               string COMMENT  '全部剔除包内件件量(运单号)'
   ,all_scan_piece               string COMMENT  '全部剔除包号件量(运单号)'
   ,all_bag_piece                string COMMENT  '全部剔除散单和包内件件量(运单号)'
   ,create_date                   string COMMENT '任务单创建时间'
   ,stat_time                     string COMMENT '执行小时时间'
   , `dt`                          string COMMENT 'time'

)COMMENT '中心进出港车辆报表明细临时表导出数据-小时更新'
    PARTITIONED BY (
    is_port_detail                TINYINT COMMENT '是否进港/出港 1:进港 2:出港')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_tmp.db/external/tmp_dm_oper_center_ship_port_detail_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );



CREATE EXTERNAL TABLE `jms_dm`.`dm_tab_center_ship_summary_dt`
(
    center_code           string comment '中心code',
    center_name           string comment '中心name',
    agent_code            string comment '代理区code',
    agent_name            string comment '代理区name',
    ydwx_car_cnt          bigint comment '进港已到未卸车辆数',
    ydwx_piece_cnt        bigint comment '进港已到未卸总件数',
    ydwx_part_cnt         bigint comment '进港已到未卸货量（万票）',
    ydwx_scan_cnt         bigint comment '进港已到未卸货量（万件）',
    ydwx_bag_cnt          bigint comment '进港已到未卸货量（包）',
    yxwf_car_cnt          bigint comment '进港已卸未发车辆数',
    yxwf_piece_cnt        bigint comment '进港已卸未发总件数',
    yxwf_part_cnt         bigint comment '进港已到未卸货量（万票）',
    yxwf_scan_cnt         bigint comment '进港已到未卸货量（万件）',
    yxwf_bag_cnt          bigint comment '进港已到未卸货量（包）',
    zt_car_cnt            bigint comment '进港在途车辆数',
    zt_piece_cnt          bigint comment '进港在途总件数',
    zt_part_cnt           bigint comment '进港在途货量（万票）',
    zt_scan_cnt           bigint comment '进港在途货量（万件）',
    zt_bag_cnt            bigint comment '进港在途货量（包）',
    out_ydwx_car_cnt      bigint comment '出港已到未卸车辆数',
    out_ydwx_piece_cnt    bigint comment '出港已到未卸总件数',
    out_ydwx_part_cnt     bigint comment '出港已到未卸货量（万票）',
    out_ydwx_scan_cnt     bigint comment '出港已到未卸货量（万件）',
    out_ydwx_bag_cnt      bigint comment '出港已到未卸货量（包）',
    out_yxwf_car_cnt      bigint comment '出港已卸未发车辆数',
    out_yxwf_piece_cnt    bigint comment '出港已卸未发总件数',
    out_yxwf_part_cnt     bigint comment '出港已到未卸货量（万票）',
    out_yxwf_scan_cnt     bigint comment '出港已到未卸货量（万件）',
    out_yxwf_bag_cnt      bigint comment '出港已到未卸货量（包）',
    out_zt_car_cnt        bigint comment '出港在途车辆数',
    out_zt_piece_cnt      bigint comment '出港在途总件数',
    out_zt_part_cnt       bigint comment '出港在途货量（万票）',
    out_zt_scan_cnt       bigint comment '出港在途货量（万件）',
    out_zt_bag_cnt        bigint comment '出港在途货量（包）',
    date_time             date comment '统计日期',
    stat_time             String comment  '计算小时区间'
)
  COMMENT '中心进出港车辆报表明细临时表导出数据-小时更新'
    PARTITIONED BY (
    `dt` string)
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_tab_center_ship_summary_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );